//
//  DBControal.swift
//  NewsAPP
//
//  Created by URNing B on 2021/5/26.
//

import Foundation


class DBControl
{
    static var CollectionCount = 0
    static func initDB()
    {
        let sqlite = SQLiteManager.sharedInstance
        
        if !sqlite.openDB() {return}
        
        let createSql = "CREATE TABLE IF NOT EXISTS CollectionNews('id' INTEGER NOT NULL PRIMARY KEY, " +
            " 'path' TEXT, 'image' TEXT, 'title' TEXT, 'passtime' TEXT);"
        
        if !sqlite.execNoneQuerySQL(sql: createSql){sqlite.closeDB();return}
        
        let cleanAllnews = "DELETE FROM CollectionNews"
        if !sqlite.execNoneQuerySQL(sql: cleanAllnews){sqlite.closeDB(); return}
        
        let resetNews = "DELETE FROM sqlite_sequence WHERE name = 'CollectionNews';"
        if !sqlite.execNoneQuerySQL(sql: resetNews){sqlite.closeDB(); return}
        
        sqlite.closeDB()
    }
    
    static func GetCollectionNews()
    {
        let sqlite = SQLiteManager.sharedInstance
        
        if !sqlite.openDB(){return}
        
        let queryResult = sqlite.execQuerySQL(sql: "SELECT * FROM CollectionNews")
        
        for row in queryResult!
        {
            print(row)
        }
        sqlite.closeDB()
    }
    
    static func collectNews(news: News)
    {
        let sqlite = SQLiteManager.sharedInstance
        print(CollectionCount)
        let stu0 = "INSERT INTO CollectionNews(id,path,image,title,passtime) VALUES('\(CollectionCount + 1)','\(news.path)','\(news.image)','\(news.title)','\(news.passtime)');"
        if !sqlite.openDB(){return}
        let queryResult = sqlite.execQuerySQL(sql: "SELECT * FROM CollectionNews")
        var arepeat = false
        for row in queryResult!
        {
            if row["path"] as! String == news.path
            {
                arepeat = true
                print("重复收藏")
            }
        }
        if arepeat == false
        {
            CollectionCount += 1
            if !sqlite.execNoneQuerySQL(sql: stu0) {sqlite.closeDB(); return}
        }
    }
    
    static func getCollectionNewsPath(id:Int) -> String
    {
        let sqlite = SQLiteManager.sharedInstance
        
        if !sqlite.openDB(){return "open database error"}
        
        let queryResult = sqlite.execQuerySQL(sql: "SELECT path FROM CollectionNews WHERE id = '\(id)'")
        let path = (queryResult![0]["path"]!)
        
        sqlite.closeDB()
        
        return "\(path)"
    }
    
    static func getCollectionNewsImage(id:Int) -> String
    {
        let sqlite = SQLiteManager.sharedInstance
        
        if !sqlite.openDB(){return "open database error"}
        
        let queryResult = sqlite.execQuerySQL(sql: "SELECT image FROM CollectionNews WHERE id = '\(id)'")
        let image = (queryResult![0]["image"]!)
        
        sqlite.closeDB()
        
        return "\(image)"
    }
    
    static func getCollectionNewsTitle(id:Int) -> String
    {
        let sqlite = SQLiteManager.sharedInstance
        if !sqlite.openDB(){return "open database error"}
        let queryResult = sqlite.execQuerySQL(sql: "SELECT title FROM CollectionNews WHERE id = '\(id)'")
        let title = (queryResult![0]["title"]!)
        sqlite.closeDB()        
        return "\(title)"
    }
    
    static func getCollectionNewsTime(id:Int) -> String
    {
        let sqlite = SQLiteManager.sharedInstance
        
        if !sqlite.openDB(){return "open database error"}
        
        let queryResult = sqlite.execQuerySQL(sql: "SELECT passtime FROM CollectionNews WHERE id = '\(id)'")
        let time = (queryResult![0]["passtime"]!)
        
        sqlite.closeDB()
        
        return "\(time)"
    }
    
    static func deleteCollectionNews(id:Int)
    {
        DBControl.CollectionCount -= 1
        
        let sqlite = SQLiteManager.sharedInstance
        
        if !sqlite.openDB(){return}
        
        print("DELETE")
        
        let deleteNews = "DELETE FROM CollectionNews WHERE id = '\(id)'"
        
        if !sqlite.execNoneQuerySQL(sql: deleteNews){sqlite.closeDB(); return}
        
        let updateNews = "UPDATE CollectionNews SET id = id - 1 WHERE id > '\(id)'"
        
        if !sqlite.execNoneQuerySQL(sql: updateNews){sqlite.closeDB(); return}
        

        sqlite.closeDB()
    }
}
